package com.wyz.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.wyz.dto.UpdateScoreDto;
import com.wyz.entity.ScoreEntity;

import com.wyz.vo.StudentScoreVo;
import org.apache.ibatis.annotations.*;
;

import java.util.List;
/**
 * @author 权佳升
 * @date 2024/12/23 16:30
 */

@Mapper
public interface ScoreMapper extends BaseMapper<ScoreEntity> {

    // 添加学生成绩
    @Insert("INSERT INTO t_score (coursetable_id, score) " +
            "SELECT t_coursetable.coursetable_id, #{score} " +
            "FROM t_coursetable " +
            "JOIN t_student ON t_coursetable.student_id = t_student.student_id " +
            "WHERE t_student.student_id = #{studentId}")
    Integer addScoreByStudentId(@Param("studentId") Integer studentId, @Param("score") Integer score);

    // 更新学生成绩
    @Update("UPDATE t_score " +
            "JOIN t_coursetable ON t_score.coursetable_id = t_coursetable.coursetable_id " +
            "JOIN t_student st ON t_coursetable.student_id = t_student.student_id " +
            "SET t_score.score = #{score} " +
            "WHERE t_student.student_id = #{studentId}") // 修正了表名部分和 WHERE 子句中的 st
    boolean updateScoreByStudentId(@Param("score") Integer score, @Param("studentId") Integer studentId);

    // 学生ID查询成绩
    @Select("SELECT t_score.score, t_student.student_id, t_student.student_name, t_course.course_name " +
            "FROM t_score " +
            "JOIN t_coursetable ON t_score.coursetable_id = t_coursetable.coursetable_id " +
            "JOIN t_student ON t_coursetable.student_id = t_student.student_id " +
            "JOIN t_course ON t_coursetable.course_id = t_course.course_id " +
            "WHERE t_student.student_id = #{studentId}")
    List<StudentScoreVo> findScoresByStudentId(@Param("studentId") Integer studentId);

    // 学生姓名模糊查询
    @Select("SELECT t_score.score, t_student.student_id, t_student.student_name, t_course.course_name " +
            "FROM t_score " +
            "JOIN t_coursetable ON t_score.coursetable_id = t_coursetable.coursetable_id " +
            "JOIN t_student ON t_coursetable.student_id = t_student.student_id " +
            "JOIN t_course ON t_coursetable.course_id = t_course.course_id " +
            "WHERE t_student.student_name LIKE CONCAT('%', #{name}, '%')")
    List<StudentScoreVo> findScoresByStudentName(@Param("name") String name);
    @Select("SELECT t_score.score, t_student.student_id, t_student.student_name, t_course.course_name " +
            "FROM t_score " +
            "JOIN t_coursetable ON t_score.coursetable_id = t_coursetable.coursetable_id " +
            "JOIN t_student ON t_coursetable.student_id = t_student.student_id " +
            "JOIN t_course ON t_coursetable.course_id = t_course.course_id " +
            "WHERE t_course.course_id = #{courseId}")
    List<StudentScoreVo> getScoresByCourseId(@Param("courseId") Integer courseId);

    IPage<StudentScoreVo> selectPageByStudentId(IPage<StudentScoreVo> page, @Param("studentId") Integer studentId);

    // 查询所有学生成绩
    @Select("SELECT t_score.score, t_student.student_id, t_student.student_name, t_course.course_name " +
            "FROM t_score  " +
            "JOIN t_coursetable  ON t_score.coursetable_id = t_coursetable.coursetable_id " +
            "JOIN t_student  ON t_coursetable.student_id = t_student.student_id " +
            "JOIN t_course  ON t_coursetable.course_id = t_course.course_id")
    List<StudentScoreVo> findAllStudentScores();
    @Insert("INSERT INTO t_score (coursetable_id) VALUES (#{coursetableId})")
    int insertScore(ScoreEntity scoreEntity);
    @Delete("DELETE from t_score where coursetable_id=#{coursetableId}")
    int deleteCoursetableId(Integer coursetableId);
}
